package renguanyu.app.tools.model.anjuke;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import renguanyu.app.tools.util.DbUtil;

public class AjkShDao {

	private QueryRunner queryRunner = DbUtil.getQueryRunner();

	public List<Map<String, Object>> queryLowTotalAvgPrice(String communityName) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select  ");
		sql.append("code ");
		sql.append(",name ");
		sql.append(",`structure` ");
		sql.append(",area ");
		sql.append(",total_price ");
		sql.append(",avg_price ");
		sql.append(",CONCAT('https://daqing.anjuke.com/prop/view/',code) as url ");
		sql.append(",toward ");
		sql.append(",community_address ");
		sql.append(",`floor` ");
		sql.append(",anxuan ");
		sql.append("from anjuke_second_house as second_house ");
		sql.append("where 1=1 ");
		sql.append("AND created_at = (SELECT max(created_at) FROM anjuke_second_house) ");
		sql.append("and anxuan='Y' ");
		sql.append("and community_name = '" + communityName + "' ");
		sql.append("order by total_price asc,avg_price asc ");
		return queryRunner.query(sql.toString(), new MapListHandler());
	}

	public List<Map<String, Object>> queryLowAvgPrice(String communityName) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select  ");
		sql.append("code ");
		sql.append(",name ");
		sql.append(",`structure` ");
		sql.append(",area ");
		sql.append(",total_price ");
		sql.append(",avg_price ");
		sql.append(",CONCAT('https://daqing.anjuke.com/prop/view/',code) as url ");
		sql.append(",toward ");
		sql.append(",community_address ");
		sql.append(",`floor` ");
		sql.append(",anxuan ");
		sql.append("from anjuke_second_house as second_house ");
		sql.append("where 1=1 ");
		sql.append("AND created_at = (SELECT max(created_at) FROM anjuke_second_house) ");
		sql.append("and anxuan='Y' ");
		sql.append("and community_name = '" + communityName + "' ");
		sql.append("order by avg_price asc  ");
		return queryRunner.query(sql.toString(), new MapListHandler());
	}

	public List<Map<String, Object>> queryDayNew(String communityName) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select ");
		sql.append("code ");
		sql.append(",name ");
		sql.append(",`structure` ");
		sql.append(",area ");
		sql.append(",total_price ");
		sql.append(",avg_price ");
		sql.append(",CONCAT('https://daqing.anjuke.com/prop/view/',code) as url  ");
		sql.append(",toward ");
		sql.append(",community_name ");
		sql.append(",community_address ");
		sql.append(",`floor` ");
		sql.append(",anxuan ");
		sql.append(",created_at ");
		sql.append("from anjuke_second_house ");
		sql.append("where created_at = (select max(created_at) from anjuke_second_house) ");
		sql.append("and code not in ( ");
		sql.append("	select distinct code ");
		sql.append("	from anjuke_second_house ");
		sql.append("	where created_at != (select max(created_at) from anjuke_second_house) ");
		sql.append(") ");
		sql.append("and anxuan = 'Y' ");
		sql.append("and community_name = '" + communityName + "' ");
		sql.append("order by avg_price   ");
		return queryRunner.query(sql.toString(), new MapListHandler());
	}

}
